package com.fau.fm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.fm.entity.MedicalAdmin;
import com.fau.fm.entity.MedicalCallCustomer;
import com.fau.fm.entity.MedicalCity;
import com.fau.fm.entity.MedicalDistrict;
import com.fau.fm.entity.MedicalEnterprise;
import com.fau.fm.entity.MedicalFinancialClaim;
import com.fau.fm.entity.MedicalFinancialOrders;
import com.fau.fm.entity.MedicalKeyValue;
import com.fau.fm.entity.MedicalLogistics;
import com.fau.fm.entity.MedicalMediaInfo;
import com.fau.fm.entity.MedicalOrderBackSection;
import com.fau.fm.entity.MedicalOrderHasBackSection;
import com.fau.fm.entity.MedicalOrderHasReconciliation;
import com.fau.fm.entity.MedicalOrderReconClaim;
import com.fau.fm.entity.MedicalOrderReconciliation;
import com.fau.fm.entity.MedicalOrdersInfo;
import com.fau.fm.entity.MedicalOrdersLog;
import com.fau.fm.entity.MedicalOrdersProduct;
import com.fau.fm.entity.MedicalOrdersStorage;
import com.fau.fm.entity.MedicalOrdersWaitDelivery;
import com.fau.fm.entity.MedicalProvince;
import com.fau.fm.service.ICheckingManagerService;
import com.fau.fm.util.Page;
import com.fau.fm.util.ParamUtil;
import com.fau.fm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class CheckingManagerService extends BaseService implements ICheckingManagerService {

	@Override
	public Page findAllFinancialByPage(Map<String, Object> map, MedicalFinancialOrders order, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalFinancialOrders m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalFinancialOrders m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(order.getPayType());
		
		if(null != order.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(order.getStartTime());
		}
		if(null != order.getEndTime()) {
			statusSql.append(" and m.createTime < ? ");
			countSql.append(" and m.createTime < ? ");
			objectList.add(order.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean save(MedicalFinancialClaim order, MedicalAdmin admin) {
		String ids[] = order.getOrderId().split(",");
		if(ids.length == 1) {
			MedicalFinancialOrders financial = (MedicalFinancialOrders) this.iBaseDao.getObject(MedicalFinancialOrders.class, ids[0]);
			if(null == financial) {
				return false;
			} else {
				MedicalFinancialClaim calim = new MedicalFinancialClaim();
				BeanUtils.copyProperties(financial, calim);
				calim.setClaimUserId(admin.getId());
				this.iBaseDao.addObject(calim);
				this.iBaseDao.updateHql("delete from MedicalFinancialOrders m where m.id = ? ", ids[0]);
				return true;
			}
		} else {
			if(includeHasClaimed(ids)) {
				for(String id : ids) {
					MedicalFinancialOrders financial = (MedicalFinancialOrders) this.iBaseDao.getObject(MedicalFinancialOrders.class, id);
					if(null != financial) {
						MedicalFinancialClaim calim = new MedicalFinancialClaim();
						BeanUtils.copyProperties(financial, calim);
						calim.setClaimUserId(admin.getId());
						this.iBaseDao.addObject(calim);
						this.iBaseDao.updateHql("delete from MedicalFinancialOrders m where m.id = ? ", id);
					}
				}
				return true;
			} else {
				return false;
			}
		}
	}

	/**
	 * @desc 判断是否包含已认领的订单
	 * @param ids
	 * @return
	 * @author dangjh
	 * @datetime 2014年11月23日,上午11:29:01
	 */
	private boolean includeHasClaimed(String[] ids) {
		if(ids.length == 0) {
			return true;
		} else {
			StringBuilder hql = new StringBuilder("select count(*) from MedicalFinancialOrders m where m.id in ( ");
			List<Object> list = new LinkedList<Object>();
			for(int i=0;i<ids.length;i++) {
				if(i == 0){
					hql.append("?");
					list.add(ids[i]);
				}else{
					hql.append(",?");
					list.add(ids[i]);
				}
			}
			hql.append(") ");
			int count = this.iBaseDao.getIntCount(hql.toString(),list.toArray());
			if(ids.length > count) {
				return false;
			} else {
				return true;
			}
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean update(MedicalFinancialClaim order) {
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, order.getHospitalId());
		// 判断企业是否需要财务人员进行对账、回款
		String status = enterprise.getNeedStatus();
		if(SystemConfig.HAVE_NONE.equals(status)) {
			return false;
		}
		String[] ids = order.getOrderId().split(",");
		if(ids.length == 1) {
			MedicalFinancialClaim claim = (MedicalFinancialClaim) this.iBaseDao.getObject(MedicalFinancialClaim.class, ids[0]);
			MedicalOrderReconciliation reconciliation = new MedicalOrderReconciliation();
			BeanUtils.copyProperties(claim, reconciliation);
			reconciliation.setUpdateTime(new Date());
			if(SystemConfig.HAVE_RECONCILED.equals(status)) {
				//此处设置 回款状态为已认领
				reconciliation.setClaimStatus(SystemConfig.HAS_CLAIMED);
			} else if(SystemConfig.HAVE_ALL.equals(status)){
				reconciliation.setClaimStatus(SystemConfig.UN_CLAIMED);
			}
			this.iBaseDao.addObject(reconciliation);
			//我的已对账统计实体类
			MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
			BeanUtils.copyProperties(reconciliation, hasReconciliation);
			this.iBaseDao.addObject(hasReconciliation);
			
			// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
			if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
				MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
				MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, hasReconciliation.getId());
				BeanUtils.copyProperties(sotrage, delivery);
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_RECONCILED, ids[0]);
				this.iBaseDao.addObject(delivery);
			} else {
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, ids[0]);
			}
			
			this.iBaseDao.updateHql("delete from MedicalFinancialClaim m where m.id = ? ", ids[0]);
			return true;
		} else {
			for(String id : ids) {
				MedicalFinancialClaim claim = (MedicalFinancialClaim) this.iBaseDao.getObject(MedicalFinancialClaim.class, id);
				MedicalOrderReconciliation reconciliation = new MedicalOrderReconciliation();
				BeanUtils.copyProperties(claim, reconciliation);
				reconciliation.setUpdateTime(new Date());
				
				if(SystemConfig.HAVE_RECONCILED.equals(status)) {
					//此处设置 回款状态为已认领
					reconciliation.setClaimStatus(SystemConfig.HAS_CLAIMED);
				} else if(SystemConfig.HAVE_ALL.equals(status)){
					reconciliation.setClaimStatus(SystemConfig.UN_CLAIMED);
				}
				
				this.iBaseDao.addObject(reconciliation);
			
				//我的已对账统计实体类
				MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
				BeanUtils.copyProperties(reconciliation, hasReconciliation);
				this.iBaseDao.addObject(hasReconciliation);
				
				// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
				if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
					MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
					MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, id);
					BeanUtils.copyProperties(sotrage, delivery);
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_RECONCILED, id);
					this.iBaseDao.addObject(delivery);
				} else {
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, id);
				}
				
				this.iBaseDao.updateHql("delete from MedicalFinancialClaim m where m.id = ? ", id);
			}
			return true;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean updateWaitClaim(MedicalFinancialClaim order) {
		// 查询所属企业
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, order.getHospitalId());
		// 判断企业是否需要财务人员进行对账、回款
		String status = enterprise.getNeedStatus();
		if(SystemConfig.HAVE_NONE.equals(status)) {
			return false;
		}
		String[] ids = order.getOrderId().split(",");
		if(ids.length == 1) {
			MedicalFinancialOrders claim = (MedicalFinancialOrders) this.iBaseDao.getObject(MedicalFinancialOrders.class, ids[0]);
			MedicalOrderReconciliation reconciliation = new MedicalOrderReconciliation();
			BeanUtils.copyProperties(claim, reconciliation);
			reconciliation.setClaimUserId(order.getClaimUserId());
			reconciliation.setUpdateTime(new Date());
			if(SystemConfig.HAVE_RECONCILED.equals(status)) {
				// 企业只拥有对账操作
				reconciliation.setClaimStatus(SystemConfig.HAS_CLAIMED);
			} else {
				// 企业拥有对账和回款操作
				reconciliation.setClaimStatus(SystemConfig.UN_CLAIMED);
			}
			this.iBaseDao.addObject(reconciliation);
			//我的已对账统计实体类
			MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
			BeanUtils.copyProperties(reconciliation, hasReconciliation);
			this.iBaseDao.addObject(hasReconciliation);
			
			// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账待发货信息
			if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
				MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
				MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, hasReconciliation.getId());
				BeanUtils.copyProperties(sotrage, delivery);
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_RECONCILED, ids[0]);
				this.iBaseDao.addObject(delivery);
			} else {
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, ids[0]);
			}
			
			this.iBaseDao.updateHql("delete from MedicalFinancialOrders m where m.id = ? ", ids[0]);
			return true;
		} else {
			for(String id : ids) {
				MedicalFinancialOrders claim = (MedicalFinancialOrders) this.iBaseDao.getObject(MedicalFinancialOrders.class, id);
				MedicalOrderReconciliation reconciliation = new MedicalOrderReconciliation();
				BeanUtils.copyProperties(claim, reconciliation);
				reconciliation.setClaimUserId(order.getClaimUserId());
				reconciliation.setUpdateTime(new Date());
				if(SystemConfig.HAVE_RECONCILED.equals(status)) {
					// 企业只拥有对账操作
					reconciliation.setClaimStatus(SystemConfig.HAS_CLAIMED);
				} else if(SystemConfig.HAVE_ALL.equals(status)) {
					// 企业拥有对账和回款操作
					reconciliation.setClaimStatus(SystemConfig.UN_CLAIMED);
				}
				this.iBaseDao.addObject(reconciliation);
				
				//我的已对账统计实体类
				MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
				BeanUtils.copyProperties(reconciliation, hasReconciliation);
				this.iBaseDao.addObject(hasReconciliation);
				
				// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
				if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
					MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
					MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, id);
					BeanUtils.copyProperties(sotrage, delivery);
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_RECONCILED, id);
					this.iBaseDao.addObject(delivery);
				} else {
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, id);
				}
				
				this.iBaseDao.updateHql("delete from MedicalFinancialOrders m where m.id = ? ", id);
			}
			return true;
		}
	}
	
	@Override
	public Page findAllOrderReconciliation(Map<String, Object> map, MedicalFinancialClaim order, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderReconciliation m where 1=1 and m.hospitalId = ? and m.claimUserId = ? and m.payType = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderReconciliation m where 1=1 and m.hospitalId = ? and m.claimUserId = ? and m.payType = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(admin.getId());
		objectList.add(order.getPayType());
		
		if(null != order.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(order.getStartTime());
		}
		if(null != order.getEndTime()) {
			statusSql.append(" and m.createTime < ? ");
			countSql.append(" and m.createTime < ? ");
			objectList.add(order.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	public Page findAllOrderClaim(Map<String, Object> map, MedicalFinancialClaim claim, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalFinancialClaim m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalFinancialClaim m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(claim.getPayType());
		
		if(null != claim.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(claim.getStartTime());
		}
		if(null != claim.getEndTime()) {
			statusSql.append(" and m.createTime < ? ");
			countSql.append(" and m.createTime < ? ");
			objectList.add(claim.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}
//****************************************************************************************//
	@Override
	public Page findAllReconByPage(Map<String, Object> map, MedicalFinancialOrders order, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderReconciliation m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimStatus = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderReconciliation m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimStatus = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(order.getPayType());
		objectList.add(SystemConfig.UN_CLAIMED);
		
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		// 针对线下汇款  只查询已经发货的订单
		if(SystemConfig.HAVE_ALL.equals(enterprise.getNeedStatus())) {
			if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(order.getPayType())) {
				statusSql.append(" and m.warehouseStatus = ? ");
				countSql.append(" and m.warehouseStatus = ? ");
				objectList.add(SystemConfig.HAS_DELIVERY);
			}
		}
		
		if(null != order.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(order.getStartTime());
		}
		if(null != order.getEndTime()) {
			statusSql.append(" and m.createTime < ? ");
			countSql.append(" and m.createTime < ? ");
			objectList.add(order.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveRecon(MedicalOrderReconClaim order, MedicalAdmin admin) {
		String ids[] = order.getOrderId().split(",");
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		if(ids.length == 1) {
			MedicalOrderReconciliation financial = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, ids[0]);
			if(null == financial) {
				return false;
			} else {
				MedicalOrderReconClaim claim = new MedicalOrderReconClaim();
				BeanUtils.copyProperties(financial, claim);
				claim.setClaimUserId(admin.getId());
				if("2".equals(enterprise.getNeedStatus())) {
					this.iBaseDao.updateHql("delete from MedicalOrderReconciliation m where m.id = ? ", ids[0]);
				} else {
					this.iBaseDao.updateHql("update MedicalOrderReconciliation m set m.claimStatus = ? where m.id = ? ", SystemConfig.HAS_CLAIMED, ids[0]);
				}
				return this.iBaseDao.addObject(claim);
			}
		} else {
			if(includeHasClaimedRecon(ids)) {
				for(String id : ids) {
					MedicalOrderReconciliation financial = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, id);
					if(null != financial) {
						MedicalOrderReconClaim claim = new MedicalOrderReconClaim();
						BeanUtils.copyProperties(financial, claim);
						claim.setClaimUserId(admin.getId());
						if("2".equals(enterprise.getNeedStatus())) {
							this.iBaseDao.updateHql("delete from MedicalOrderReconciliation m where m.id = ? ", id);
						} else {
							this.iBaseDao.updateHql("update MedicalOrderReconciliation m set m.claimStatus = ? where m.id = ? ", SystemConfig.HAS_CLAIMED, id);
						}
						this.iBaseDao.addObject(claim);
					}
				}
				return true;
			} else {
				return false;
			}
		}
	}

	/**
	 * @desc 判断是否包含已认领的订单（回款操作）
	 * @param ids
	 * @return boolean
	 * @author dangjh
	 * @datetime 2014年11月23日,上午11:29:01
	 */
	private boolean includeHasClaimedRecon(String[] ids) {
		if(ids.length == 0) {
			return true;
		} else {
			StringBuilder hql = new StringBuilder("select count(*) from MedicalOrderReconciliation m where m.claimStatus = ? and m.id in (");
			List<Object> list = new LinkedList<Object>();
			list.add(SystemConfig.HAS_CLAIMED);
			for(int i=0;i<ids.length;i++) {
				if(i == 0){
					hql.append("?");
					list.add(ids[i]);
				}else{
					hql.append(",?");
					list.add(ids[i]);
				}
			}
			hql.append(")");
			int count = this.iBaseDao.getIntCount(hql.toString(), list.toArray());
			if(count == 0) {
				return true;
			} else {
				return false;
			}
		}
	}
	
	@Override
	public Page findAllOrderReconClaim(Map<String, Object> map, MedicalOrderReconClaim claim, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderReconClaim m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimUserId = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderReconClaim m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimUserId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(claim.getPayType());
		objectList.add(admin.getId());
		
		if(null != claim.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(claim.getStartTime());
		}
		
		if(null != claim.getEndTime()) {
			statusSql.append(" and m.createTime < ? ");
			countSql.append(" and m.createTime < ? ");
			objectList.add(claim.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean updateRecon(MedicalOrderReconClaim order) {
		String[] ids = order.getOrderId().split(",");
		if(ids.length == 1) {
			MedicalOrderReconClaim claim = (MedicalOrderReconClaim) this.iBaseDao.getObject(MedicalOrderReconClaim.class, ids[0]);
			MedicalOrderBackSection section = new MedicalOrderBackSection();
			BeanUtils.copyProperties(claim, section);
			section.setUpdateTime(new Date());
			this.iBaseDao.addObject(section);
			
			//我的已回款统计实体类
			MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
			BeanUtils.copyProperties(section, hasReconciliation);
			this.iBaseDao.addObject(hasReconciliation);
			
			// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
			if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
				MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
				MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ids[0]);
				BeanUtils.copyProperties(sotrage, delivery);
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_SECTIONED, ids[0]);
				this.iBaseDao.addObject(delivery);
			} else {
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, ids[0]);
			}
			return this.iBaseDao.updateHql("delete from MedicalOrderReconClaim m where m.id = ? ", ids[0]);
		} else {
			for(String id : ids) {
				MedicalOrderReconClaim claim = (MedicalOrderReconClaim) this.iBaseDao.getObject(MedicalOrderReconClaim.class, id);
				MedicalOrderBackSection section = new MedicalOrderBackSection();
				BeanUtils.copyProperties(claim, section);
				section.setUpdateTime(new Date());
				this.iBaseDao.addObject(section);
				
				//我的已回款统计实体类
				MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
				BeanUtils.copyProperties(section, hasReconciliation);
				this.iBaseDao.addObject(hasReconciliation);
				
				// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
				if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
					MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
					MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ids[0]);
					BeanUtils.copyProperties(sotrage, delivery);
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_SECTIONED, id);
					this.iBaseDao.addObject(delivery);
				} else {
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, id);
				}
				
				this.iBaseDao.updateHql("delete from MedicalOrderReconClaim m where m.id = ? ", id);
			}
			return true;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveWaitRecon(MedicalOrderReconClaim order, MedicalAdmin admin) {
		String[] ids = order.getOrderId().split(",");
		if(ids.length == 1) {
			MedicalOrderReconciliation claim = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, ids[0]);
			MedicalOrderBackSection section = new MedicalOrderBackSection();
			BeanUtils.copyProperties(claim, section);
			section.setClaimUserId(admin.getId());
			section.setUpdateTime(new Date());
			this.iBaseDao.addObject(section);
			
			//我的已回款统计实体类
			MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
			BeanUtils.copyProperties(section, hasReconciliation);
			this.iBaseDao.addObject(hasReconciliation);
			
			// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
			if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
				MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
				MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ids[0]);
				BeanUtils.copyProperties(sotrage, delivery);
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_SECTIONED, ids[0]);
				this.iBaseDao.addObject(delivery);
			} else {
				//更新总订单表状态
				this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, ids[0]);
			}
			return this.iBaseDao.updateHql("delete from MedicalOrderReconciliation m where m.id = ? ", ids[0]);
		} else {
			for(String id : ids) {
				MedicalOrderReconciliation claim = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, id);
				MedicalOrderBackSection section = new MedicalOrderBackSection();
				BeanUtils.copyProperties(claim, section);
				section.setClaimUserId(admin.getId());
				section.setUpdateTime(new Date());
				this.iBaseDao.addObject(section);
				
				//我的已回款统计实体类
				MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
				BeanUtils.copyProperties(section, hasReconciliation);
				this.iBaseDao.addObject(hasReconciliation);
				
				// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
				if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
					MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
					MedicalOrdersStorage sotrage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, id);
					BeanUtils.copyProperties(sotrage, delivery);
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.warehouseStatus = ?, m.financialStatus = ? where m.id = ? ", SystemConfig.BE_DELIVERY, SystemConfig.HAS_SECTIONED, id);
					this.iBaseDao.addObject(delivery);
				} else {
					//更新总订单表状态
					this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, id);
				}
				
				this.iBaseDao.updateHql("delete from MedicalOrderReconciliation m where m.id = ? ", id);
			}
			return true;
		}
	}
	
	@Override
	public Page findAllOrderBackSection(Map<String, Object> map, MedicalOrderReconClaim order, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderBackSection m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimUserId = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderBackSection m where 1=1 and m.hospitalId = ? and m.payType = ? and m.claimUserId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(order.getPayType());
		objectList.add(admin.getId());
		
		if(null != order.getStartTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(order.getStartTime());
		}
		
		if(null != order.getEndTime()) {
			statusSql.append(" and m.createTime > ? ");
			countSql.append(" and m.createTime > ? ");
			objectList.add(order.getEndTime());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	public Workbook selectedPage(MedicalFinancialClaim order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			for(int j=0;j<ids.length;j++){
				// 获得记录
				MedicalOrderReconciliation bean = (MedicalOrderReconciliation)this.iBaseDao.getObject(MedicalOrderReconciliation.class, ids[j]);
				if(null != bean){
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(bean.getSerialNum());
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(bean.getName());
					currentRow.createCell(4).setCellValue(bean.getPayment().toString());
					currentRow.createCell(5).setCellValue(bean.getFreight().toString());
					if(null != bean.getSignTime()) {
						currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allPage(MedicalFinancialClaim order) throws Exception {
		List<MedicalOrderReconciliation> list = this.iBaseDao.getBySql("from MedicalOrderReconciliation m where 1=1 and m.hospitalId = ? and m.claimUserId = ? ", null, 
				order.getHospitalId(), order.getClaimUserId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalOrderReconciliation bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getPayment().toString());
				currentRow.createCell(5).setCellValue(bean.getFreight().toString());
				if(null != bean.getSignTime()) {
					currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}

	@Override
	public Workbook selectedPage(MedicalOrderReconClaim order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			for(int j=0;j<ids.length;j++){
				// 获得记录
				MedicalOrderBackSection bean = (MedicalOrderBackSection)this.iBaseDao.getObject(MedicalOrderBackSection.class, ids[j]);
				if(null != bean){
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(bean.getSerialNum());
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(bean.getName());
					currentRow.createCell(4).setCellValue(bean.getPayment().toString());
					currentRow.createCell(5).setCellValue(bean.getFreight().toString());
					if(null != bean.getSignTime()) {
						currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allPage(MedicalOrderReconClaim order) throws Exception {
		List<MedicalOrderBackSection> list = this.iBaseDao.getBySql("from MedicalOrderBackSection m where 1=1 and m.hospitalId = ? and m.claimUserId = ? ", null, 
				order.getHospitalId(), order.getClaimUserId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalOrderBackSection bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getPayment().toString());
				currentRow.createCell(5).setCellValue(bean.getFreight().toString());
				if(null != bean.getSignTime()) {
					currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importDelivery(MedicalAdmin admin, File file, String type) throws Exception {
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		// 判断企业是否需要财务人员进行对账、回款
		String status = enterprise.getNeedStatus();
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet =  workbook.getSheetAt(0);
		StringBuilder errorInfo = new StringBuilder();
		int sucNum = 0; 	// 成功数量
		int failNum = 0;	// 失败数量
		//遍历每行
		for (Iterator<Row> rows = sheet.rowIterator(); rows.hasNext();) {
			Row row = rows.next();
			if(null == row.getCell(0)){ break; }
			if(row.getRowNum() >= 1){//跳过标题和表头
				String expressNo = null;		// 编号
			 	if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
			 		expressNo = row.getCell(0).getStringCellValue().trim();//编号
			 	} else {
			 		expressNo = String.format("%.0f",row.getCell(0).getNumericCellValue());
			 	}
				if(StringUtils.isBlank(expressNo)){ continue; }
				if(SystemConfig.WAIT_RECONCILED_CASH.equals(type)) {
					MedicalFinancialOrders claim = (MedicalFinancialOrders) this.iBaseDao.getObjectByHql("from MedicalFinancialOrders m where m.expressNo=? ", expressNo);
					if(null != claim){
				    	MedicalOrderReconciliation recon = new MedicalOrderReconciliation();
						BeanUtils.copyProperties(claim, recon);
						// 此处设置 回款状态为未认领(认领后方可进行回款操作)
						if(SystemConfig.HAVE_RECONCILED.equals(status)) {
							recon.setClaimStatus(SystemConfig.HAS_CLAIMED);
						} if(SystemConfig.HAVE_ALL.equals(status)) {
							recon.setClaimStatus(SystemConfig.UN_CLAIMED);
						}
						recon.setUpdateTime(new Date());
						recon.setClaimUserId(admin.getId());
						this.iBaseDao.addObject(recon);
						
						// 我的已对账统计实体类
						MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
						BeanUtils.copyProperties(recon, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					 } else {
						 ++failNum;
						 errorInfo.append(expressNo+",");
					 }
				} else if(SystemConfig.MY_WAIT_RECONCILED_CASH.equals(type)) {
					MedicalFinancialClaim claim = (MedicalFinancialClaim) this.iBaseDao.getObjectByHql("from MedicalFinancialClaim m where m.expressNo=? ", expressNo);
					if(null != claim){
				    	MedicalOrderReconciliation recon = new MedicalOrderReconciliation();
						BeanUtils.copyProperties(claim, recon);
						// 此处设置 回款状态为未认领(认领后方可进行回款操作)
						if(SystemConfig.HAVE_RECONCILED.equals(status)) {
							recon.setClaimStatus(SystemConfig.HAS_CLAIMED);
						} if(SystemConfig.HAVE_ALL.equals(status)) {
							recon.setClaimStatus(SystemConfig.UN_CLAIMED);
						}
						recon.setUpdateTime(new Date());
						recon.setClaimUserId(admin.getId());
						this.iBaseDao.addObject(recon);
						
						// 我的已对账统计实体类
						MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
						BeanUtils.copyProperties(recon, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					 } else {
						 ++failNum;
						 errorInfo.append(expressNo+",");
					 }
				}
			}
		}
		String str = "成功"+sucNum+"条,失败"+failNum+"条\n失败快递单号"+errorInfo.toString();
		return str;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importLineDelivery(MedicalAdmin admin, File file, String type) throws Exception {
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		// 判断企业是否需要财务人员进行对账、回款
		String status = enterprise.getNeedStatus();
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet =  workbook.getSheetAt(0);
		StringBuilder errorInfo = new StringBuilder();
		int sucNum = 0; 	// 成功数量
		int failNum = 0;	// 失败数量
		//遍历每行
		for (Iterator<Row> rows = sheet.rowIterator(); rows.hasNext();) {
			Row row = rows.next();
			if(null == row.getCell(0)){ break; }
			if(row.getRowNum() >= 1){//跳过标题和表头
				String serialNum = null;		// 编号
			 	if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
			 		serialNum = row.getCell(0).getStringCellValue().trim();//编号
			 	} else {
			 		serialNum = String.format("%.0f",row.getCell(0).getNumericCellValue());
			 	}
				if(StringUtils.isBlank(serialNum)){ continue; }
				if(SystemConfig.WAIT_RECONCILED_LINE.equals(type)) {
					MedicalFinancialOrders claim = (MedicalFinancialOrders) this.iBaseDao.getObjectByHql("from MedicalFinancialOrders m where m.serialNum=? ", serialNum);
					if(null != claim){
						MedicalOrderReconciliation recon = new MedicalOrderReconciliation();
						BeanUtils.copyProperties(claim, recon);
						// 此处设置 回款状态为未认领(认领后方可进行回款操作)
						if(SystemConfig.HAVE_RECONCILED.equals(status)) {
							recon.setClaimStatus(SystemConfig.HAS_CLAIMED);
						} if(SystemConfig.HAVE_ALL.equals(status)) {
							recon.setClaimStatus(SystemConfig.UN_CLAIMED);
						}
						recon.setUpdateTime(new Date());
						recon.setClaimUserId(admin.getId());
						this.iBaseDao.addObject(recon);
						
						// 我的已对账统计实体类
						MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
						BeanUtils.copyProperties(recon, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(serialNum+",");
					}
				} else if(SystemConfig.MY_WAIT_RECONCILED_LINE.equals(type)) {
					MedicalFinancialClaim claim = (MedicalFinancialClaim) this.iBaseDao.getObjectByHql("from MedicalFinancialClaim m where m.serialNum=? ", serialNum);
					if(null != claim){
						MedicalOrderReconciliation recon = new MedicalOrderReconciliation();
						BeanUtils.copyProperties(claim, recon);
						// 此处设置 回款状态为未认领(认领后方可进行回款操作)
						if(SystemConfig.HAVE_RECONCILED.equals(status)) {
							recon.setClaimStatus(SystemConfig.HAS_CLAIMED);
						} if(SystemConfig.HAVE_ALL.equals(status)) {
							recon.setClaimStatus(SystemConfig.UN_CLAIMED);
						}
						recon.setUpdateTime(new Date());
						recon.setClaimUserId(admin.getId());
						this.iBaseDao.addObject(recon);
						
						// 我的已对账统计实体类
						MedicalOrderHasReconciliation hasReconciliation = new MedicalOrderHasReconciliation();
						BeanUtils.copyProperties(recon, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 对账完成后需向仓储发送已对账带发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_RECONCILED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(serialNum+",");
					}
				}
			}
		}
		String str = "成功"+sucNum+"条,失败"+failNum+"条\n失败订单号"+errorInfo.toString();
		return str;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importReconDelivery(MedicalAdmin admin, File file, String type) throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet =  workbook.getSheetAt(0);
		StringBuilder errorInfo = new StringBuilder();
		int sucNum = 0; 	// 成功数量
		int failNum = 0;	// 失败数量
		//遍历每行
		for (Iterator<Row> rows = sheet.rowIterator(); rows.hasNext();) {
			Row row = rows.next();
			if(row.getCell(0) == null){ break; }
			if(row.getRowNum() >= 1){//跳过标题和表头
				String expressNo = null;		// 编号
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
					expressNo = row.getCell(0).getStringCellValue().trim();//编号
				} else {
					expressNo = String.format("%.0f",row.getCell(0).getNumericCellValue());
				}
				if(StringUtils.isBlank(expressNo)){ continue; }
				if(SystemConfig.WAIT_SECTION_CASH.equals(type)) {
					MedicalOrderReconciliation claim = (MedicalOrderReconciliation) this.iBaseDao.getObjectByHql("from MedicalOrderReconciliation m where m.expressNo=? ", expressNo);
					if(null != claim){
						MedicalOrderBackSection section = new MedicalOrderBackSection();
						BeanUtils.copyProperties(claim, section);
						section.setUpdateTime(new Date());
						this.iBaseDao.addObject(section);
						//我的已回款统计实体类
						MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
						BeanUtils.copyProperties(section, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
//						// 如果付款方式是线下付款, 回款完成后需向仓储发送已回款待发货信息
//						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType())) {
//							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
//							BeanUtils.copyProperties(hasReconciliation, delivery);
//							this.iBaseDao.addObject(delivery);
//						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(expressNo+",");
					}
				} else if(SystemConfig.MY_WAIT_SECTION_CASH.equals(type)) {
					MedicalOrderReconClaim claim = (MedicalOrderReconClaim) this.iBaseDao.getObjectByHql("from MedicalOrderReconClaim m where m.expressNo=? ", expressNo);
					if(null != claim){
						MedicalOrderBackSection section = new MedicalOrderBackSection();
						BeanUtils.copyProperties(claim, section);
						section.setUpdateTime(new Date());
						this.iBaseDao.addObject(section);
						//我的已回款统计实体类
						MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
						BeanUtils.copyProperties(section, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(expressNo+",");
					}
				}
			}
		}
		String str = "成功"+sucNum+"条,失败"+failNum+"条\n失败快递单号"+errorInfo.toString();
		return str;
	}

	@Override
	public String importLineReconDelivery(MedicalAdmin admin, File file, String type) throws Exception {
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		// 判断企业是否需要财务人员进行对账、回款
		String status = enterprise.getNeedStatus();
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet =  workbook.getSheetAt(0);
		StringBuilder errorInfo = new StringBuilder();
		int sucNum = 0; 	// 成功数量
		int failNum = 0;	// 失败数量
		//遍历每行
		for (Iterator<Row> rows = sheet.rowIterator(); rows.hasNext();) {
			Row row = rows.next();
			if(row.getCell(0) == null){ break; }
			if(row.getRowNum() >= 1){//跳过标题和表头
				String serialNum = null;		// 编号
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
					serialNum = row.getCell(0).getStringCellValue().trim();//编号
				} else {
					serialNum = String.format("%.0f",row.getCell(0).getNumericCellValue());
				}
				if(StringUtils.isBlank(serialNum)){ continue; }
				if(SystemConfig.WAIT_SECTION_LINE.equals(type)) {
					MedicalOrderReconciliation claim = (MedicalOrderReconciliation) this.iBaseDao.getObjectByHql("from MedicalOrderReconciliation m where m.serialNum=? ", serialNum);
					if(null != claim){
						MedicalOrderBackSection section = new MedicalOrderBackSection();
						BeanUtils.copyProperties(claim, section);
						section.setUpdateTime(new Date());
						this.iBaseDao.addObject(section);
						//我的已回款统计实体类
						MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
						BeanUtils.copyProperties(section, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 回款完成后需向仓储发送已回款待发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType()) && SystemConfig.HAVE_SECTION.equals(status)) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(serialNum+",");
					}
				} else if(SystemConfig.MY_WAIT_SECTION_LINE.equals(type)) {
					MedicalOrderReconClaim claim = (MedicalOrderReconClaim) this.iBaseDao.getObjectByHql("from MedicalOrderReconClaim m where m.serialNum=? ", serialNum);
					if(null != claim){
						MedicalOrderBackSection section = new MedicalOrderBackSection();
						BeanUtils.copyProperties(claim, section);
						section.setUpdateTime(new Date());
						this.iBaseDao.addObject(section);
						//我的已回款统计实体类
						MedicalOrderHasBackSection hasReconciliation = new MedicalOrderHasBackSection();
						BeanUtils.copyProperties(section, hasReconciliation);
						this.iBaseDao.addObject(hasReconciliation);
						
						// 如果付款方式是线下付款, 回款完成后需向仓储发送已回款待发货信息
						if(SystemConfig.OFFLINE_PAYMENT_STATUS.equals(hasReconciliation.getPayType()) && SystemConfig.HAVE_SECTION.equals(status)) {
							MedicalOrdersWaitDelivery delivery = new MedicalOrdersWaitDelivery();
							BeanUtils.copyProperties(hasReconciliation, delivery);
							this.iBaseDao.addObject(delivery);
						}
						
						//更新总订单表状态
						this.iBaseDao.updateHql("update MedicalOrdersStorage m set m.financialStatus = ? where m.id = ? ", SystemConfig.HAS_SECTIONED, claim.getId());
						this.iBaseDao.deleteObject(claim);
						++sucNum;
					} else {
						++failNum;
						errorInfo.append(serialNum+",");
					}
				}
			}
		}
		String str = "成功"+sucNum+"条,失败"+failNum+"条\n失败订单号"+errorInfo.toString();
		return str;
	}
	
	@Override
	public MedicalOrdersInfo findById(MedicalAdmin admin, String id) {
		MedicalOrdersInfo info = new MedicalOrdersInfo();
		MedicalOrdersStorage order = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, id);
		if(null != order) {
			info.setId(order.getId());
			info.setSerialNum(order.getSerialNum());
			info.setName(order.getName());
			info.setPhone(order.getContactNo());
			MedicalProvince province = (MedicalProvince) this.iBaseDao.getObjectByHql("from MedicalProvince p where p.id = ? ", order.getProvinceId());
			info.setProvince(province.getpName());
			MedicalCity city = (MedicalCity) this.iBaseDao.getObjectByHql("from MedicalCity c where c.id = ? ", order.getCityId());
			info.setCity(city.getcName());
			MedicalDistrict district = (MedicalDistrict) this.iBaseDao.getObjectByHql("from MedicalDistrict d where d.id = ? ", order.getDistrictId());
			info.setDistrict(district.getdName());
			info.setAddress(order.getAddress());
			info.setPostCode(order.getZipCode());
			info.setDescription(order.getOrdersRemark());
			MedicalKeyValue keyVal1 = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), SystemConfig.OM_SYSTEM_TYPE, order.getInvoice());
			if(null != keyVal1) {
				info.setInvoice(keyVal1.getKeyValue());
			}
			MedicalMediaInfo media = (MedicalMediaInfo) this.iBaseDao.getObject(MedicalMediaInfo.class, order.getMediaId());
			if(null != media) {
				info.setMediaName(media.getName());
			}
			MedicalKeyValue keyVal2 = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), SystemConfig.OM_SYSTEM_TYPE, order.getNature());
			if(null != keyVal2) {
				info.setOrderNature(keyVal2.getKeyValue());
			}
			MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
			if(null != logistics) {
				info.setLogistics(logistics.getName());
			}
			MedicalKeyValue keyVal3 = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), SystemConfig.OM_SYSTEM_TYPE, order.getPayType());
			if(null != keyVal3) {
				info.setPayType(keyVal3.getKeyValue());
			}
			info.setPayment(order.getPayment());
			info.setFreight(order.getFreight());
			info.setReceivable(order.getReceivable());
			info.setAmountPaid(order.getAmountPaid());
			MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, order.getCustomerId());
			info.setBalance(customer.getMoney());
			info.setUsePoints(order.getUsePoints());
			info.setPoints(order.getPoints());
			info.setAfterUsePoints(customer.getIntegral());
			info.setUseRed(order.getUseRed());
			info.setRed(order.getRed());
			info.setAfterUseRed(customer.getRedEnvelopes());
		}
		return info;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersProduct> findByOrderId(String orderId) {
		List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, orderId);
		return ordersProductList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersLog> findAllOrdersLog(String eid, String orderNo) {
		return this.iBaseDao.getBySql("from MedicalOrdersLog m where m.eid = ? and m.orderNo = ? ", null, eid, orderNo);
	}

}
